package dataAccessLayer.factoryMySQL.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dataAccessLayer.abstractFactory.daos.DAOpies;
import dataAccessLayer.dataBase.DBManager;
import dataAccessLayer.visualObject.VOpie;

public class DAOpiesMySQL implements DAOpies
{
	private DBManager dbManager;
	
	public DAOpiesMySQL(DBManager dbManager) 
	{
		this.dbManager = dbManager;
	}

	public int insertPie(VOpie vOpie) 
	{
		int code = -1;

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		Connection connection = dbManager.getConnection();

		try
		{
			preparedStatement = connection.prepareStatement("Insert INTO pies (name,image,recipe,price,pastryCook) VALUES (?,?,?,?,?)");

			preparedStatement.setString(1, vOpie.getName());
			preparedStatement.setString(2, vOpie.getImage());
			preparedStatement.setInt(3, vOpie.getRecipe());
			preparedStatement.setInt(4, vOpie.getPrice());
			preparedStatement.setInt(5, vOpie.getPastryCook());

			preparedStatement.execute();

			preparedStatement = connection.prepareStatement("SELECT code FROM pies ORDER BY code DESC LIMIT 1");

			resultSet = preparedStatement.executeQuery();

			resultSet.next();

			code = resultSet.getInt(1);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return code;
	}
	
	public boolean updatePie(VOpie vOpie) 
	{
		PreparedStatement preparedStatement = null;

		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("UPDATE pies set name=?,image=?,recipe=?,price=?,pastryCook=?  where code=?");
			preparedStatement.setString(1, vOpie.getName());
			preparedStatement.setString(2, vOpie.getImage());
			preparedStatement.setInt(3, vOpie.getRecipe());
			preparedStatement.setInt(4, vOpie.getPrice());
			preparedStatement.setInt(5, vOpie.getPastryCook());
			preparedStatement.setInt(6, vOpie.getCode());
			
			preparedStatement.execute();
			
			return true;
			
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return false;
	}

	public boolean removePie(VOpie vOpie) 
	{
		PreparedStatement preparedStatement = null;

		Connection connection = dbManager.getConnection();
		
		try {
			preparedStatement = connection.prepareStatement("DELETE from pies where code=?");
			preparedStatement.setInt(1, vOpie.getCode());
			
			preparedStatement.execute();
			
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return false;
	}
	
	public ArrayList<VOpie> listPies() 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resulSet=null;
		Connection connection = dbManager.getConnection();
		ArrayList<VOpie> searchPies=new ArrayList<VOpie>();
		
		
		try 
		{
			preparedStatement = connection.prepareStatement("SELECT code,name,image,recipe,price,pastryCook FROM pies");			
			resulSet=preparedStatement.executeQuery();
			
			while(resulSet.next())
			{
				VOpie search=new VOpie();
				search.setCode(resulSet.getInt(1));
				search.setName(resulSet.getString(2));
				search.setImage(resulSet.getString(3));
				search.setRecipe(resulSet.getInt(4));
				search.setPrice(resulSet.getInt(5));
				search.setPastryCook(resulSet.getInt(6));
				searchPies.add(search);
			}
			
			return searchPies;
			
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}	
		finally
		{
			try
			{
				
				preparedStatement.close();
				resulSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return null;
	}
	
	public VOpie searchPieByCode(VOpie vOpie) 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resulSet = null;
		Connection connection = dbManager.getConnection();

		VOpie pieSearch = null;

		try
		{
			preparedStatement = connection.prepareStatement("SELECT code,name,image,recipe,price,pastryCook FROM pies WHERE code=?");

			preparedStatement.setInt(1, vOpie.getCode());

			resulSet = preparedStatement.executeQuery();
			
			resulSet.next();

			pieSearch = new VOpie();
			pieSearch.setCode(resulSet.getInt(1));
			pieSearch.setName(resulSet.getString(2));
			pieSearch.setImage(resulSet.getString(3));
			pieSearch.setRecipe(resulSet.getInt(4));
			pieSearch.setPrice(resulSet.getInt(5));
			pieSearch.setPastryCook(resulSet.getInt(6));
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
				resulSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return pieSearch;
	}

	public VOpie searchPieByName(VOpie vOpie) 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resulSet = null;
		Connection connection = dbManager.getConnection();

		VOpie pieSearch = null;

		try
		{
			preparedStatement = connection.prepareStatement("SELECT code,name,image,recipe,price,pastryCook FROM pies WHERE name=?");

			preparedStatement.setString(1, vOpie.getName());

			resulSet = preparedStatement.executeQuery();
			
			resulSet.next();

			pieSearch = new VOpie();
			pieSearch.setCode(resulSet.getInt(1));
			pieSearch.setName(resulSet.getString(2));
			pieSearch.setImage(resulSet.getString(3));
			pieSearch.setRecipe(resulSet.getInt(4));
			pieSearch.setPrice(resulSet.getInt(5));
			pieSearch.setPastryCook(resulSet.getInt(6));
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
				resulSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return pieSearch;
	}
	
}
